package com.bj58.groupbuy.action.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class MostDao {
	
	public  static List<Long> getMostFollows( int cityid,int count ) throws Exception{
		
		Connection con = DBConnectionPool.getConnection(1);	//10.3.13.27_stat
		Statement st = null;
		ResultSet rs = null;
		
		String sql = "SELECT groupbuy_id,SUM(pv) as tpv FROM `item_city_pv_hour` where city_id=%d group by stat_date ,groupbuy_id order by tpv desc LIMIT %d ;";
		
		sql = String.format(sql, cityid,count);
		
		List<Long> list = new ArrayList<Long>();
		
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			int k = 0; 	//请求数据
			while (rs.next()) {
				long gbid = rs.getLong(1);
				list.add(gbid);
				
				k++;
			}
			
			if(k <count ){
				Calendar cal=Calendar.getInstance();
		        cal.add(Calendar.DATE,-1);
		        Date time=cal.getTime();		        
		        SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd"); 
		        
				sql = "SELECT groupbuy_id,pv as tpv FROM `item_city_pv_day` where city_id=%d and stat_date='%s' group by stat_date ,groupbuy_id order by tpv desc LIMIT %d ;";
				
				sql = String.format(sql, cityid,sdf.format(time),count);
				st = con.createStatement();
				rs = st.executeQuery(sql);
				
				while (rs.next()) {
					long gbid = rs.getLong(1);
					list.add(gbid);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return list;
	}
}
